package document.dao;


import db.DbConn;
import document.entity.Document;
import project.dao.ProjectDao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DocumentDao {

    /**
     * 项目成员和项目经理查看自己所在项目的文档列表
     * @param documentName
     * @param page
     * @param limit
     * @return
     */
    public List<Document> documentList(String documentName, int page, int limit, int userId) {
        List<Document> documentList = new ArrayList<>();
        String sql = "select t_document.* from t_document, t_user, t_project_user,t_project where t_user.id="+userId+
                " and t_user.id = t_project_user.user_id and t_project.id = t_project_user.project_id and t_document.project_id=t_project.id";
        if(documentName != null && (documentName.length()!=0)) {
            sql += " and fileName like '%" + documentName + "%'";
        }
        int start = (page-1)*limit;
        int end = limit;
        sql = sql + " limit " + start + "," + end;
        System.out.println("构造的sql语句是："+sql);
        try {
            Connection connection = DbConn.getconn("db_project");
            PreparedStatement ps = connection.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Document document = new Document();
                int id = rs.getInt(1);
                String fileName = rs.getString("fileName");
                String fileType = rs.getString("fileType");
                String projectName = rs.getString("projectName");
                String uploadTime = rs.getString("uploadTime");
                String uploadPerson = rs.getString("uploadPerson");
                document.setId(id);
                document.setFileName(fileName);
                document.setFileType(fileType);
                document.setProjectName(projectName);
                document.setUploadTime(uploadTime);
                document.setUploadPerson(uploadPerson);
                documentList.add(document);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return documentList;
    }

    /**
     * 返回文档上传路径
     * @param documentId
     * @return
     */
    public String getDocumentUrlById(int documentId) {
        String url = null;
        String sql = "SELECT uploadPath as url FROM t_document WHERE id = "+documentId;
        try {
            Connection connection = DbConn.getconn("db_project");
            PreparedStatement ps = connection.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                url = rs.getString("url");
            }
        }  catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return url;
    }

    /**
     * 项目主管获取所有的文档列表
     * @param documentName
     * @param page
     * @param limit
     * @return
     */
    public List<Document> documentAllList(String documentName, int page, int limit) {
        List<Document> documentList = new ArrayList<>();
        String sql = "SELECT * FROM t_document";
        if(documentName != null && (documentName.length()!=0)) {
            sql += " WHERE fileName like '%" + documentName + "%'";
        }
        int start = (page-1)*limit;
        int end = limit;
        sql = sql + " limit " + start + "," + end;
        System.out.println("构造的sql语句是："+sql);
        try {
            Connection connection = DbConn.getconn("db_project");
            PreparedStatement ps = connection.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Document document = new Document();
                int id = rs.getInt(1);
                Map<String, Object> map = findCheckResultById(id);
                String fileName = rs.getString("fileName");
                String fileType = rs.getString("fileType");
                String projectName = rs.getString("projectName");
                String uploadTime = rs.getString("uploadTime");
                String uploadPerson = rs.getString("uploadPerson");
                document.setId(id);
                document.setFileName(fileName);
                document.setFileType(fileType);
                document.setProjectName(projectName);
                document.setUploadTime(uploadTime);
                document.setUploadPerson(uploadPerson);
                document.setReview((String) map.get("review"));
                document.setScore((Integer) map.get("score"));
                documentList.add(document);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return documentList;
    }

    /**
     * 通过文档id查询审核表结果
     * @param document_id
     * @return
     */
    public Map<String, Object> findCheckResultById(int document_id) {
        Map<String, Object> map = new HashMap<>();
        String sql = "SELECT review, score FROM t_check WHERE document_id="+document_id;
        System.out.println("构造的sql语句是："+sql);
        try {
            Connection connection = DbConn.getconn("db_project");
            PreparedStatement ps = connection.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                String review = rs.getString("review");
                int score = rs.getInt("score");
                map.put("review",review);
                map.put("score",score);
                map.put("document_id",document_id);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return map;
    }

    /**
     * 对文档进行审核
     * @param document
     */
    public void checkDocument(Document document) {
        String sql = "UPDATE t_check SET review=?,score=? WHERE document_id=?";
        System.out.println("构造的sql语句是："+sql);
        try {
            Connection connection = DbConn.getconn("db_project");
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1,document.getReview());
            ps.setInt(2,document.getScore());
            ps.setInt(3,document.getId());
            ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    /**
     * 上传文档
     * @param document
     * @return
     */
    public boolean addDocument(Document document) {

        int projectId = document.getProject_id();
        ProjectDao projectDao = new ProjectDao();
        String projectName = projectDao.findProjectNameById(projectId);

        String sql = "INSERT INTO t_document(id, project_id,projectName,fileType,fileName,uploadTime,uploadPerson,uploadPath) VALUES(?,?,?,?,?,?,?,?)";
        System.out.println("构造的sql语句是："+sql);
        boolean bool = false;
        Connection connection = DbConn.getconn("db_project");
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setInt(1, document.getId());
            ps.setInt(2,projectId);
            ps.setString(3,projectName);
            ps.setString(4,document.getFileType());
            ps.setString(5,document.getFileName());
            ps.setString(6, document.getUploadTime());
            ps.setString(7,document.getUploadPerson());
            ps.setString(8,document.getUploadPath());
            ps.executeUpdate();

            //关联文档 check表
            bool = relationCheck(document.getId());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return bool;
    }

    private boolean relationCheck(int document_id) {
        boolean bool = false;
        String sql = "INSERT INTO t_check(document_id) VALUES(?)";
        System.out.println("构造的sql语句是："+sql);
        try {
            Connection connection = DbConn.getconn("db_project");
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setInt(1, document_id);
            int rs = ps.executeUpdate();
            if(rs > 0) {
                bool = true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return bool;
    }
}
